package com.jxdinfo.hussar.example.common.utils;

import com.jxdinfo.hussar.example.common.config.PropertyConfig;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.*;

@Component
public class AccessUtils {

    @Resource
    private  PropertyConfig propertyConfig1;

    private  static PropertyConfig propertyConfig;

    @PostConstruct
    public void init() {
        propertyConfig = propertyConfig1;
    }
    /**
     * 读取.mdb文件
     */
    public static List<Map<String, String>> resolverMdb(String[] file) throws Exception {
        //FTP配置
        String server = propertyConfig.getServer();
        int port = propertyConfig.getPort();
        String user = propertyConfig.getUsername();
        String word = propertyConfig.getPassword();
        //FTP对象
        FTPClient ftpClient = new FTPClient();
        //连接FTP
        ftpClient.connect(server, port);
        ftpClient.login(user, word);
        //文件传输类型
        ftpClient.setFileType(FTP.BINARY_FILE_TYPE);
        ftpClient.enterLocalPassiveMode();
        List<Map<String, String>> dataList = new ArrayList<>();
        for (String s : file) {
            //临时文件
            File tempFile = File.createTempFile("tmp",".mdb");
            tempFile.deleteOnExit();

            // 下载文件
            OutputStream outputStream = new FileOutputStream(tempFile);
            boolean success = ftpClient.retrieveFile("/" + s, outputStream);
            outputStream.close();
            if (success){
                Properties prop = new Properties();
                //设置编码
                prop.put("charSet", "UTF-8");
                prop.put("user", propertyConfig.getName());
                prop.put("password", propertyConfig.getWord());
                //数据地址
                String dbUrl = "jdbc:ucanaccess://" + tempFile.getAbsolutePath();
                //引入驱动
                Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();
                Connection conn = null;
                ResultSet tables = null;
                PreparedStatement preparedStatement = null;
                ResultSet rs = null;
                try {
                    //连接数据库资源
                    conn = DriverManager.getConnection(dbUrl, prop);
                    tables = conn.getMetaData().getTables(tempFile.getAbsolutePath(), null, null, new String[]{"TABLE"});
                    Map<String, String> map = new HashMap<>();
                    //遍历获取多张表数据
                    while (tables.next()) {
                        String tableName = tables.getString(3);
                        preparedStatement = conn.prepareStatement("select * from " + tableName + " order by 时间 DESC limit 1");
                        rs = preparedStatement.executeQuery();
                        ResultSetMetaData data = rs.getMetaData();
                        while (rs.next()) {
                            for (int i = 1; i <= data.getColumnCount(); i++) {
                                //列名
                                String columnName = data.getColumnName(i);
                                map.put(columnName, rs.getString(i));
                            }
                        }
                    }
                    dataList.add(map);
                }catch(Exception e) {
                    e.printStackTrace();
                } finally{
                    closeA11(conn, preparedStatement, tables, rs);
                }
            }
        }
        return dataList;
    }


    /**
     * 关闭所有的资源链接
     * @param conn 连接
     * @param preparedStatement 初始化 执行语句
     * @param tables 结果集
     * @param rs 结果集
     */
    private static void closeA11(Connection conn, PreparedStatement preparedStatement, ResultSet tables, ResultSet rs) {
        try {
            if (null != rs) {
                rs.close();
            }
            if (null!= tables) {
                tables.close();
            }
            if (null != preparedStatement) {
                preparedStatement.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (Exception ignore) {}
    }
}
